﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;

namespace OPENXMLLib
{
    public class XDoc : IDisposable
    {
        private Dictionary<string, XSheet> _xSheetDict = new Dictionary<string, XSheet>();

        SpreadsheetDocument _doc;
        Stream _stream;

        public XDoc(string filePath)
        {
            _stream = new FileStream(filePath, FileMode.Open);

            _doc = SpreadsheetDocument.Open(_stream, false);
        }

        public void Dispose()
        {
            _stream.Close();
        }

        public DataTable GetData(string sheetName)
        {
            IEnumerable<Sheet> sheets = _doc.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                throw new Exception(string.Format("在文档中没有发现名为:\"{0}\"的Sheet!", sheetName));
            }
            Sheet sheet = sheets.First();
            WorksheetPart worksheetPart = (WorksheetPart)_doc.WorkbookPart.GetPartById(sheet.Id);

            List<Column> columns = worksheetPart.Worksheet.GetFirstChild<Columns>().Elements<Column>().ToList();

            DataTable data = new DataTable();
            foreach (var column in columns)
            {
                data.Columns.Add(CellExtention.ToAA((int)column.Min.Value));
            }

            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            List<Row> allRows = sheetData.Elements<Row>().ToList();
            foreach (Row row in allRows)
            {
                DataRow dtRow = data.NewRow();
                data.Rows.Add(dtRow);

                foreach (DataColumn dtColumn in data.Columns)
                {
                    Cell cell = row.GetCell(dtColumn.ColumnName);
                    if (cell != null)
                    {
                        dtRow[dtColumn] = cell.GetValue();
                    }
                }
            }
            
            return data;
        }

        public XSheet GetSheet(string sheetName)
        {
            IEnumerable<Sheet> sheets = _doc.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).ToList();
            if (sheets.Count() == 0)
            {
                //找出合适前提的sheet,没有则返回
                return null;
            }
            Sheet sheet = sheets.First();

            if (!_xSheetDict.ContainsKey(sheet.Name))
            {
                WorksheetPart worksheetPart = (WorksheetPart)_doc.WorkbookPart.GetPartById(sheet.Id);
                XSheet xSheet = new XSheet(sheet, worksheetPart);
                _xSheetDict.Add(sheet.Name, xSheet);
                return xSheet;
            }
            else
            {
                return _xSheetDict[sheet.Name];
            }
        }
    }
}
